package nckd.wnqjt.tmc.tda.plugin.form;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import kd.bos.algo.DataSet;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.QueryServiceHelper;

/**
 * @author husheng
 * @date 2024-11-04 20:08
 * @description
 */
public class PayablebillTermListDataPlugin extends AbstractReportListDataPlugin {
    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) {
        Date currentDate = this.getCurrentDate();
        // 收票登记
        String payablebillSql = "id,company nckd_company,company.name companyName,company.number companyNumber,bizdate,draftbilltype.number draftbilltypeNumber,amount,draftbillstatus,draftbillexpiredate";
        QFilter filter = new QFilter("rptype", QCP.equals, "paybill")
                .and("draftbilltype.number", QCP.in, new String[]{"102", "104", "CDMT-79A2C33B"})
                .and("billstatus", QCP.equals, "C")
                .and("bizdate", QCP.less_equals, currentDate)
                .and("draftbillstatus", QCP.in, new String[]{"registered", "payoffed"})
                .and("draftbilltranstatus", QCP.equals, "success");
        DataSet cdmPayablebill = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cdm_payablebill", payablebillSql, filter.toArray(), null);

        // 根据票据状态分开取值
        DataSet cdmPayablebill1 = cdmPayablebill.copy().filter("draftbillstatus = 'registered'");
        DataSet cdmPayablebill2 = cdmPayablebill.copy().filter("draftbillstatus = 'payoffed'");

        // 业务处理    业务日期大于查询日期
        QFilter filter1 = new QFilter("bizdate", QCP.large_than, currentDate)
                .and("tradetype", QCP.equals, "payoff");
        DataSet drafttradebill = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cdm_drafttradebill", "id,entrys.draftbill", filter1.toArray(), null);

        DataSet finish = cdmPayablebill2.join(drafttradebill).on("id", "entrys.draftbill").select(cdmPayablebill2.getRowMeta().getFieldNames()).finish();

        // 合并结果集
        DataSet payablebill = cdmPayablebill1.union(finish);

        // 获取所有组织id
        DataSet companyDataSet = payablebill.copy().groupBy(new String[]{"nckd_company"}).finish();

        // 根据 票据到期日-查询日 分类
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        // 银行承兑
        DataSet payablebill1 = payablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill2 = payablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 60 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill3 = payablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 90 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 60")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill4 = payablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 120 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 90")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill5 = payablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 150 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 120")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill6 = payablebill.copy().filter("draftbilltypeNumber = '102' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 180 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 150")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        // 商业承兑
        DataSet payablebill7 = payablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill8 = payablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 60 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill9 = payablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 90 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 60")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill10 = payablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 120 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 90")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill11 = payablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 150 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 120")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill12 = payablebill.copy().filter("draftbilltypeNumber = '104' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 180 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 150")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        // 类票据
        DataSet payablebill13 = payablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill14 = payablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 60 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 30")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill15 = payablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 90 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 60")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill16 = payablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 120 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 90")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill17 = payablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 150 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 120")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();
        DataSet payablebill18 = payablebill.copy().filter("draftbilltypeNumber = 'CDMT-79A2C33B' and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') <= 180 and DATEDIF(to_date('" + sdf.format(currentDate) + "','yyyy-MM-dd'),draftbillexpiredate,'D') > 150")
                .groupBy(new String[]{"nckd_company"}).count().sum("amount").finish();


        DataSet dataSet = companyDataSet.leftJoin(payablebill1).on("nckd_company", "nckd_company").select(new String[]{"nckd_company"}, new String[]{"count as nckd_strokecount1", "amount as nckd_amount1"}).finish();
        dataSet = dataSet.leftJoin(payablebill2).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount2", "amount as nckd_amount2"}).finish();
        dataSet = dataSet.leftJoin(payablebill3).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount3", "amount as nckd_amount3"}).finish();
        dataSet = dataSet.leftJoin(payablebill4).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount4", "amount as nckd_amount4"}).finish();
        dataSet = dataSet.leftJoin(payablebill5).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount5", "amount as nckd_amount5"}).finish();
        dataSet = dataSet.leftJoin(payablebill6).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount6", "amount as nckd_amount6"}).finish();
        dataSet = dataSet.leftJoin(payablebill7).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount7", "amount as nckd_amount7"}).finish();
        dataSet = dataSet.leftJoin(payablebill8).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount8", "amount as nckd_amount8"}).finish();
        dataSet = dataSet.leftJoin(payablebill9).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount9", "amount as nckd_amount9"}).finish();
        dataSet = dataSet.leftJoin(payablebill10).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount10", "amount as nckd_amount10"}).finish();
        dataSet = dataSet.leftJoin(payablebill11).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount11", "amount as nckd_amount11"}).finish();
        dataSet = dataSet.leftJoin(payablebill12).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount12", "amount as nckd_amount12"}).finish();
        dataSet = dataSet.leftJoin(payablebill13).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount13", "amount as nckd_amount13"}).finish();
        dataSet = dataSet.leftJoin(payablebill14).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount14", "amount as nckd_amount14"}).finish();
        dataSet = dataSet.leftJoin(payablebill15).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount15", "amount as nckd_amount15"}).finish();
        dataSet = dataSet.leftJoin(payablebill16).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount16", "amount as nckd_amount16"}).finish();
        dataSet = dataSet.leftJoin(payablebill17).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount17", "amount as nckd_amount17"}).finish();
        dataSet = dataSet.leftJoin(payablebill18).on("nckd_company", "nckd_company").select(dataSet.getRowMeta().getFieldNames(), new String[]{"count as nckd_strokecount18", "amount as nckd_amount18"}).finish();

        dataSet = dataSet.addFields(new String[]{"nckd_amount1 + nckd_amount7 + nckd_amount13", "nckd_strokecount1 + nckd_strokecount7 + nckd_strokecount13",
                        "nckd_amount2 + nckd_amount8 + nckd_amount14", "nckd_strokecount2 + nckd_strokecount8 + nckd_strokecount14",
                        "nckd_amount3 + nckd_amount9 + nckd_amount15", "nckd_strokecount3 + nckd_strokecount9 + nckd_strokecount15",
                        "nckd_amount4 + nckd_amount10 + nckd_amount16", "nckd_strokecount4 + nckd_strokecount10 + nckd_strokecount16",
                        "nckd_amount5 + nckd_amount11 + nckd_amount17", "nckd_strokecount5 + nckd_strokecount11 + nckd_strokecount17",
                        "nckd_amount6 + nckd_amount12 + nckd_amount18", "nckd_strokecount6 + nckd_strokecount12 + nckd_strokecount18"},
                new String[]{"nckd_amountsum1", "nckd_strokecountsum1", "nckd_amountsum2", "nckd_strokecountsum2",
                        "nckd_amountsum3", "nckd_strokecountsum3", "nckd_amountsum4", "nckd_strokecountsum4",
                        "nckd_amountsum5", "nckd_strokecountsum5", "nckd_amountsum6", "nckd_strokecountsum6"});

        return dataSet;
    }

    private Date getCurrentDate() {
        Calendar cal = new GregorianCalendar();
//        cal.set(11, 0);
//        cal.set(12, 0);
//        cal.set(13, 0);
//        cal.set(14, 0);
        return cal.getTime();
    }
}
